PostgreSQL コマンド COPY — ファイルとテーブルの間でデータをコピーする
はじめに
初心者にはハードルの高い公式ドキュメント、しかし早めに読めるようになっておきたいものです。そこで、SQLの学習開始を機に公式ドキュメントとの格闘を始めます。
まず最初は、コマンド COPY — ファイルとテーブルの間でデータをコピーするに挑戦します！
筆者の初心者具合: JOINブログにこれまでの経歴や初心者具合も書いております。
それでは、公式ドキュメントに沿って進みます。
概要
こちらがラスボスですね。本ブログはココと格闘する様をお届けいたします。
COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | PROGRAM 'command' | STDIN }
[ [ WITH ] ( option [, ...] ) ]
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
TO { 'filename' | PROGRAM 'command' | STDOUT }
[ [ WITH ] ( option [, ...] ) ]
ここでoptionは以下のいずれかです。
FORMAT format_name
OIDS [ boolean ]
FREEZE [ boolean ]
DELIMITER 'delimiter_character'
NULL 'null_string'
HEADER [ boolean ]
QUOTE 'quote_character'
ESCAPE 'escape_character'
FORCE_QUOTE { ( column_name [, ...] ) | * }
FORCE_NOT_NULL ( column_name [, ...] )
FORCE_NULL ( column_name [, ...] )
ENCODING 'encoding_name'
説明
COPYコマンドは大きく2種類のやりとりをします。
- COPY FROM: ファイルをテーブルに読み込むコマンド
- COPY TO: テーブルからファイルへ書き出すコマンド
また、COPY TOコマンドは書き出しの際にSELECT文で書き出す値を指定できるようです。
上記は、COPYコマンドはどういうものなのかについて、自分の言葉で言い換えたものです。
本物はこちらです。
COPYコマンドは、PostgreSQLのテーブルと標準のファイルシステムのファイル間でデータを移動します。 COPY TOコマンドはテーブルの内容をファイルにコピーします。 また、COPY FROMコマンドは、ファイルからテーブルへとデータをコピーします（この時、既にテーブルにあるデータにコピーした内容を追加します）。 また、COPY TOによりSELECT問い合わせの結果をコピーすることができます。
列リストが指定されている場合、COPYは、指定された列のデータのみをファイルからまたはファイルへコピーします。 列リストに含まれていない列がテーブル内にある場合、COPY FROMはそれらの列にデフォルトの値を挿入します。
ファイル名付きのCOPYコマンドは、PostgreSQLサーバに対して直接ファイルへの読み書きをするように命じます。 指定したファイルは必ずPostgreSQLユーザ(サーバを実行しているユーザID)からアクセスできる必要があります。 また、ファイル名はサーバから見たように指定されなければなりません。 PROGRAMが指定された場合、サーバは指定したコマンドを実行しその標準出力を読み取る、または、プログラムの標準入力に書き出します。 コマンドはサーバからの視点で指定しなければならず、また、PostgreSQLユーザによって実行できなければなりません。 STDINやSTDOUTが指定された場合、データはクライアントとサーバ間を流れます。
英語の文章を翻訳した文章ということもあるのでしょうが、読み進めて間も無く私の頭の中は読んでいても理解はしていないモードに切り替わりました。
ここで躓くわけにはいかないので、他の部分については必要に応じて読み解くとして、早速実際の値を入れて「概要」さま、「説明」さまとお近づきになりましょう。
実行環境
macOS Catalina 10.15.3
PostgreSQL 12.1
実践（初歩編）
COPY TO
概要にはなんやかんや書いてありますが、[]はあってもいいですよ、{}は|がorの意味なので、{}のなかのどれか選べますよということだろうという想定のもと、まずはシンプルにCOPY table_name FROM 'filename'で実行してみます。
事前に用意しておいたpeopleテーブルを使用します。
select * from people; name | birthday | city | email --------+------------+--------+----------------------- Harry | 1980-07-31 | Godric | hello_harry@mail.com Eddie | 1982-01-06 | London | hello_eddie@mail.com Helena | 1966-05-26 | London | hello_helena@mail.com Hannah | 1982-11-30 | London | hello_hannah@mail.com (4 rows)
COPY table_name FROM file_nameのtable_nameにpeopleを、file_nameに'people.txt'を入れて書き出します。
COPY people TO 'people.txt'; ERROR: relative path not allowed for COPY to file
早速エラーです。
相対パスはダメ = 絶対パスじゃなきゃイヤ！ と言われてしまいましたので、絶対パスでデスクトップにファイルを置くよう指定します。
COPY people TO '/Users/user_name/Desktop/people.txt'; COPY 4
無事デスクトップに書き出されました！
ファイル名の指定についてですが、パラメータのfilenameにきちんと書いてありました。
Windowsの場合にはパスの記述方法が違うそうです。参考になりそうなサイトを貼っておきます。
Pythonのモジュールの解説をしているページですが、絶対パスと相対パスについて解説があるので、わからない方は読んでみてください。
COPY FROM
次は書き出したファイルをCOPY table_name FROM file_nameで読み込みます。
COPY people_from FROM '/Users/user_name/Desktop/people.txt'; ERROR: relation "people_from" does not exist
またエラーです。
先ほどのテーブルとは別のテーブルにしようと思い、テーブル名をpeople_fromとしたのですが、そんなテーブルはないと言われてしまいました。
コピー先のテーブルが先に必要ということですね。people_fromテーブルを作成します。
CREATE TABLE people_from ( name CHARACTER VARYING(50), birthday DATE, city character varying(50), email character varying(50) PRIMARY KEY); CREATE TABLE
SELECT * FROM people_from; name | birthday | city | email ------+----------+------+------- (0 rows)
このpeople_fromテーブルにCOPY FROMします。
COPY people_from FROM '/Users/user_name/Desktop/people.txt'; COPY 4
無事、読み込めました！
SELECT * FROM people_from; name | birthday | city | email --------+------------+--------+----------------------- Harry | 1980-07-31 | Godric | hello_harry@mail.com Eddie | 1982-01-06 | London | hello_eddie@mail.com Helena | 1966-05-26 | London | hello_helena@mail.com Hannah | 1982-11-30 | London | hello_hannah@mail.com (4 rows)
テーブル指定について「説明」に書いてありました。
それでは、すでに値が入っているpeopleテーブルにCOPY FROMしたらどうなるか試してみます。
COPY people FROM '/Users/user_name/Desktop/people.txt';
実行！
ERROR: duplicate key value violates unique constraint "people_pkey" DETAIL: Key (email)=(hello_harry@mail.com) already exists. CONTEXT: COPY people, line 1
テーブルを作る際にemailをprimary keyに指定していたため、コピーを試みた値と元の値が競合しているとエラー出てきました。
では被らない場合はどうなのか、ファイルをコピーしてemailを書き換えて試してみます。
COPY people FROM '/Users/user_name/Desktop/people2.txt'; COPY 4 SELECT * FROM people; name | birthday | city | email --------+------------+--------+------------------------ Harry | 1980-07-31 | Godric | hello_harry@mail.com Eddie | 1982-01-06 | London | hello_eddie@mail.com Helena | 1966-05-26 | London | hello_helena@mail.com Hannah | 1982-11-30 | London | hello_hannah@mail.com Harry | 1980-07-31 | Godric | hello_harry@email.com Eddie | 1982-01-06 | London | hello_eddie@email.com Helena | 1966-05-26 | London | hello_helena@email.com Hannah | 1982-11-30 | London | hello_hannah@email.com (8 rows)
既存のテーブルに値が追加される形でコピーされました！
次は、先ほどの説明の引用で、読み込みの際には相対パスも指定可能と書いてありましたので、相対パスで読み込んでみます。
その前に、今どこにいるのかを把握する必要がありますが、普通にpwdと入力すると、
pwd; ERROR: syntax error at or near "pwd" LINE 1: pwd
やはりエラーが出ます。調べたところ\! pwdのように、\!でエスケープすればコマンドが使えるみたいです。
\! pwd /Users/user_name \cd Desktop/ \! ls people.txt
Desktopに people.txt があることも確認できたので、そのように相対パスを記述しましょう。(カレントディレクトリは、ホームディレクトリに戻しました。）
COPY people_from FROM '/Desktop/people.txt'; ERROR: could not open file "/Desktop/people.txt" for reading: No such file or directory HINT: COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy.
エラーが出てしまいました。
No such file or directory と言っていますが、上述の通りファイルは Desktopにあります。どういうことでしょうか。
ドキュメントを確認したところ、パスについて別の記述がありました。
pwdで示されるディレクトリからの相対パスではないということですね。色々と格闘した結果、PostgerSQLのあるディレクトリを探し出しました。
name postgres % pwd /usr/local/var/postgres
ここからの相対パスを記述して実行します。
COPY people_from FROM '../../../../Users/user_name/Desktop/people.txt';
すると、下の画像がポップアウトで出現したので、OKを押して再実行！
ERROR: could not open file "../../../../Users/user_name/Desktop/people.txt" for reading: Interrupted system call
システムコールが邪魔されたと、エラーになってしまいました。
これについてネットで検索をかけてみたのですが、いまいち原因がわかりませんでした。そして、冗談のつもりでしれっともう一度実行してみたらできるんじゃないかと思い実行したところ、何事もなく読み込めました！
今回は先ほど出てきたアクセス確認のポップアップも出てきませんでした。（そしてpeople_fromテーブルは、絶対パスで読み込んだ値をdrop tableしてあります。）
COPY people_from FROM '../../../../Users/user_name/Desktop/people.txt'; COPY 4 SELECT * FROM people_from; name | birthday | city | email --------+------------+--------+----------------------- Harry | 1980-07-31 | Godric | hello_harry@mail.com Eddie | 1982-01-06 | London | hello_eddie@mail.com Helena | 1966-05-26 | London | hello_helena@mail.com Hannah | 1982-11-30 | London | hello_hannah@mail.com (4 rows)
あのエラーは何だったのだろう？という疑問はさておき、一番シンプルな記述での書き出しと読み込みは成功したので、次は各パラメータの説明を読んでみます。
パラメータ（実践 基本編）
table_name
スキーマとは何でしょうか？ このメージが参考になったのでリンクを貼っておきます。
スキーマなんて設定した覚えないなと思っていたのですが、デフォルトはPublicだったのですね。
先人のアウトプットに感謝しながら、スキーマの確認コマンドを打ち込んでみます。
[PostgreSQL] よく使うコマンドまとめ
\d people_from
Table "public.people_from"
Column | Type | Collation | Nullable | Default
----------+-----------------------+-----------+----------+---------
name | character varying(50) | | |
birthday | date | | |
city | character varying(50) | | |
email | character varying(50) | | not null |
Indexes:
"people_from_pkey" PRIMARY KEY, btree (email)
確かに、Table "public.people_from"となっていますね。それではこれがスキーマ修飾名だと思うので、こちらでCOPY TOとCOPY FROMを試してみます。
COPY public.people_from TO '/Users/user_name/Desktop/people_schema.txt'; COPY 4
はい、何事もなく書き出せました。では次はCOPY FROMをしてみます。
COPY public.people_schema FROM '/Users/user_name/Desktop/people_schema.txt'; COPY 4 SELECT * FROM people_schema; name | birthday | city | email --------+------------+--------+----------------------- Harry | 1980-07-31 | Godric | hello_harry@mail.com Eddie | 1982-01-06 | London | hello_eddie@mail.com Helena | 1966-05-26 | London | hello_helena@mail.com Hannah | 1982-11-30 | London | hello_hannah@mail.com (4 rows)
こちらも何事もなくという感じですね。順調ですね！次のパラメーターに参りましょう。
column_name
また、列リストについては「説明」の項にもこのように書いてあります。
nameとemailを指定して書き出します。
COPY people_from (name, email) TO '/Users/user_name/Desktop/people_selcol.txt'; COPY 4
成功！
nameとemailだけ書き出されています。
次は列を指定して読み込みます。
COPY people_selcol (name, email) FROM '/Users/user_name/Desktop/people.txt'; ERROR: extra data after last expected column CONTEXT: COPY people_selcol, line 1: "Harry 1980-07-31 Godric hello_harry@mail.com"
エラーですね。「列リストに含まれていない列がテーブル内にある場合、COPY FROMはそれらの列にデフォルトの値を挿入します。」というのはデフォルトの値を指定する必要があるのでしょうか。
ALTER TABLE people_selcol ALTER COLUMN birthday SET DEFAULT '1990-01-01'; ALTER TABLE ALTER TABLE people_selcol ALTER COLUMN city SET DEFAULT 'earth'; ALTER TABLE
読み込まない列にデフォルト値を指定しました。もう一度読み込んでみます。
COPY people_selcol (name, email) FROM '/Users/user_name/Desktop/people.txt'; ERROR: extra data after last expected column CONTEXT: COPY people_selcol, line 1: "Harry 1980-07-31 Godric hello_harry@mail.com"
先ほどと全く同じエラーないようですね。というより、列で指定していない値も読み込んでいますね。
改めてテキストファイルを見ると、列名の記載がどこにもありません。これではどの列を読み込めばいいのかがわかりませんね。また、CSVファイルなら列名も一緒に書き出されるのでできるのではと試行錯誤してみたのですが、この記事を見つけてCOPY FROMでの列名指定での読み込みは断念することにしました。
さて、気を取り直して次に参りましょう。
query
よくわかりませんが、慣れ親しんでいるSELECTを使ってnameが'H'で始まる行を抽出して書き出してみます。
元のテーブルです。
SELECT * FROM people; name | birthday | city | email --------+------------+--------+------------------------ Harry | 1980-07-31 | Godric | hello_harry@mail.com Eddie | 1982-01-06 | London | hello_eddie@mail.com Helena | 1966-05-26 | London | hello_helena@mail.com Hannah | 1982-11-30 | London | hello_hannah@mail.com Harry | 1980-07-31 | Godric | hello_harry@email.com Eddie | 1982-01-06 | London | hello_eddie@email.com Helena | 1966-05-26 | London | hello_helena@email.com Hannah | 1982-11-30 | London | hello_hannah@email.com (8 rows)
実行！
COPY (SELECT * FROM people WHERE name LIKE 'H%') TO '/Users/user_name/Desktop/people_query.txt'; COPY 6
6件のデータが書き出されました。
幸先がいいですね。次はDELETEしてみます。
COPY (DELETE FROM people WHERE birthday = '1982-01-06') TO '/Users/user_name/Desktop/people_query.txt'; ERROR: COPY query must have a RETURNING clause
はい、エラーです。こちら、公式ドキュメントにきちんと書いてありました。
RETURNING *をつければ良さそうですね。
COPY (DELETE FROM people WHERE birthday = '1982-01-06' RETURNING *) TO '/Users/user_name/Desktop/people_query.txt'; COPY 2
COPY 2とな？
DELETによって削除した2行を書き出すコマンドだったんですね。てっきり指定した行以外の行が書き出されると思っていました。
DELETE FROM people WHERE birthday = '1982-01-06' RETURNING *; name | birthday | city | email -------+------------+--------+----------------------- Eddie | 1982-01-06 | London | hello_eddie@mail.com Eddie | 1982-01-06 | London | hello_eddie@email.com (2 rows)
COPY TOをつけずに実行すると、ターミナル上に削除した2行が返されました。この2行をファイルに書き出す作業をしていたのですね。 そうすると、INSERT INTOやUPDATEはどのような動作をするのでしょうか。まずはINSERT INTOから試してみます。
COPY (INSERT INTO people (name, birthday, city, email)
VALUES ('Eddie', '1982-01-06', 'London', 'hello_eddie@mail.com') RETURNING *)
TO '/Users/user_name/Desktop/people_query.txt';
COPY 1
INSERT INTOした1行がファイルに書き出され、peopleテーブルにも挿入されました。
SELECT * FROM people; name | birthday | city | email --------+------------+--------+------------------------ Harry | 1980-07-31 | Godric | hello_harry@mail.com Helena | 1966-05-26 | London | hello_helena@mail.com Hannah | 1982-11-30 | London | hello_hannah@mail.com Harry | 1980-07-31 | Godric | hello_harry@email.com Helena | 1966-05-26 | London | hello_helena@email.com Hannah | 1982-11-30 | London | hello_hannah@email.com Eddie | 1982-11-06 | London | hello_eddie@mail.com (7 rows)
次はUPDATEです。先ほど挿入したEddieの誕生日が間違っているので、正しいものに更新します。
COPY (UPDATE people set birthday = '1982-01-06' WHERE birthday = '1982-11-06' RETURNING *) TO '/Users/user_name/Desktop/people_query.txt'; COPY 1
やはり、UPDATEした1行が書き出されています。また、テーブルも更新されています。
SELECT * FROM people; name | birthday | city | email --------+------------+--------+------------------------ Harry | 1980-07-31 | Godric | hello_harry@mail.com Helena | 1966-05-26 | London | hello_helena@mail.com Hannah | 1982-11-30 | London | hello_hannah@mail.com Harry | 1980-07-31 | Godric | hello_harry@email.com Helena | 1966-05-26 | London | hello_helena@email.com Hannah | 1982-11-30 | London | hello_hannah@email.com Eddie | 1982-01-06 | London | hello_eddie@mail.com (7 rows)
まとめ
全パラメーターを試そうと思って始めましたが、断念。今回はテキストファイルを使用しましたが、CSVやTSVファイルなども対応しています。その際には書き出されたデバイスによってエンコードを指定したり、どの文字で区切られているのかをDELIMITERで指定したりする必要があって大変そうです。テキストファイルを選んだ理由は、ドキュメントにデフォルトはtxtですと書いてあったためです。
ポイントは最初にテーブルを用意しておくこと！でしょうか。pandasでread_csvを繰り返していた人間からするとつまづきポイントでした。 想像通りの大苦戦でしたが、次回もめげずに公式ドキュメントを読んだ結果をお届けしたいです。
言い回しや、認識違いなど気になる点がありましたら、指摘してください。ありがとうございました！
